﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Utility.Helper;

namespace BLL
{
    /// <summary>
    ///
    /// </summary>
    public class Yx_AccountBLL : BaseBLL
    {
        /// <summary>
        /// 添加账号
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertModel(Yx_Account entity, string operatename)
        {
            entity.Account_Id = GetSequence("SEQ_N_ACCOUNT_ID");

            var sql = @"INSERT INTO YX_ACCOUNT(
	ACCOUNTI_ID,
	COMPANY_ID,
	ACCOUNTNO,
	ACCOUNTTYPE,
	STATE,
	OPERATORID,
	OPERATORDATE,
	REMARK,
	DEPARTMENT_ID,
	N_BANK_ID
)
VALUES(
	{0},
	{1},
	'{2}',
	{3},
	{4},
	{5},
	TO_DATE('{6}', 'YYYY-MM-DD HH24:MI:SS'),
	'{7}',
	{8},
	{9}
)";
            sql = string.Format(sql,
                entity.Account_Id, entity.Company_Id, entity.Account_No, entity.Account_Type,
                entity.State, entity.Operator_Id, entity.Operator_date.ToString("yyyy-MM-dd HH:mm:ss"),
                entity.Remark, entity.Demartment_Id, entity.N_BANK_ID);
            int count = NoQuery(sql, operatename);
            return count;
        }

        public bool Insert(Yx_Account entity, string operatename)
        {
            return InsertModel(entity, operatename) > 0 ? true : false;
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns></returns>
        public DataTable SelectAll(string condition)
        {
            //            string sql = string.Format(@"select  a.accounti_id accountId,c.cmy_name companyName,d.deptname depName,decode(a.accounttype,1,'支出户',2,'收入户') accType,
            //            a.accountno accountNo,a.openbank openBank from sys_company c,sys_department d,yx_account a
            //            where c.cmy_id=d.cmy_id and c.cmy_id=a.company_id
            //            and d.deptid=a.department_id ");

            string sql = string.Format(@"select a.accounti_id accountId,
                   c.cmy_name companyName,
                   d.deptname depName,
                   decode(a.accounttype, 1, '支出户', 2, '收入户') accType,
                   a.accountno accountNo,
                   b.c_name openBank,
                   decode(a.state, 1, '启用', 0, '停用') state
              from sys_company c, sys_department d, yx_account a,sys_bank b
             where c.cmy_id = d.cmy_id
               and c.cmy_id = a.company_id
               and d.deptid = a.department_id
               and a.n_bank_id=b.n_id");

            if (sql != "")
            {
                sql += condition;
            }
            sql += "  order by a.accounti_id, a.company_id,a.department_id ";

            DataTable dt = Query(sql);

            return dt;
        }

        /// <summary>
        /// 根据编号查询账户信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Yx_Account SelectById(string id)
        {
            string sql = string.Format(@"select * from yx_account a where a.accounti_id= " + id);

            DataTable dt = Query(sql);

            Yx_Account model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new Yx_Account();
                    model.Account_Id = Convert.ToDecimal(id);
                    model.Account_No = dr["accountno"].ToString();
                    model.Account_Type = Convert.ToDecimal(dr["accounttype"].ToString());
                    model.Company_Id = Convert.ToDecimal(dr["company_id"].ToString());
                    model.Demartment_Id = Convert.ToDecimal(dr["department_id"].ToString());
                    //model.OpenBank = dr["openbank"].ToString();
                    //model.N_BANK_ID = Convert.ToDecimal(dr["openbank"].ToString());
                    model.Operator_date = Convert.ToDateTime(dr["operatordate"].ToString());
                    model.Operator_Id = Convert.ToDecimal(dr["operatorid"].ToString());
                    model.Remark = dr["remark"].ToString();
                    model.State = Convert.ToDecimal(dr["state"].ToString());
                    model.N_BANK_ID = Convert.ToDecimal(dr["N_BANK_ID"]);
                }
            }
            return model;
        }

        /// <summary>
        /// 根据条件查询账号
        /// </summary>
        /// <param name="whr"></param>
        /// <returns></returns>
        public DataTable SeleByWhr(string whr)
        {
            string sql = string.Format(@"select * from yx_account t where STATE = 1  " + whr);

            return Query(sql);
        }

        public Yx_Account Find(decimal id)
        {
            return SelectById(id.ToString());
        }

        /// <summary>
        /// 根据编号修改账号信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateById(Yx_Account entity, string operatename)
        {
            var sql = @"UPDATE YX_ACCOUNT
SET
	COMPANY_ID = {1},
	ACCOUNTNO = '{2}',
	ACCOUNTTYPE = {3},
	STATE = {4},
	OPERATORID = {5},
	OPERATORDATE = TO_DATE('{6}', 'YYYY-MM-DD HH24:MI:SS'),
	REMARK = '{7}',
	DEPARTMENT_ID = {8},
	N_BANK_ID = {9}
WHERE
	ACCOUNTI_ID = {0}";
            sql = string.Format(sql,
                entity.Account_Id, entity.Company_Id, entity.Account_No, entity.Account_Type,
                entity.State, entity.Operator_Id, entity.Operator_date.ToString("yyyy-MM-dd HH:mm:ss"),
                entity.Remark, entity.Demartment_Id, entity.N_BANK_ID);
            return NoQuery(sql, operatename);
        }

        public bool Update(Yx_Account entity, string operatename)
        {
            return UpdateById(entity, operatename) > 0 ? true : false;
        }

        /// <summary>
        /// 分页查询条件
        /// </summary>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="skip"></param>
        /// <param name="take"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine(@"select a.accounti_id accountId,
       c.cmy_name companyName,
       d.deptname depName,
       decode(a.accounttype, 1, '支出户', 2, '收入户') accType,
       a.accountno accountNo,
       b.c_name openBank,
       decode(a.state, 1, '启用', 0, '停用') state
  from sys_company c, sys_department d, yx_account a,sys_bank b
 where c.cmy_id = d.cmy_id
   and c.cmy_id = a.company_id
   and d.deptid = a.department_id
   and a.n_bank_id=b.n_id ");
            if (whereStr != null)
            {
                sqlBuilder.AppendLine(whereStr);
            }
            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        #region 下拉列表框

        public List<Yx_Account> GetEntitys(string whereStr, string orderbyStr, int skip, int take, out int count, decimal bankId)
        {
            var dt = GetDataAccount(whereStr, orderbyStr, skip, take, out count, bankId);
            var entitys = new List<Yx_Account>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ConvertToEntity(item));
            }
            return entitys;
        }

        public DataTable GetDataAccount(string whereStr, string orderbyStr, int skip, int take, out int count, decimal bankId)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("SELECT ACCOUNTI_ID,ACCOUNTNO FROM YX_ACCOUNT  WHERE STATE != 0 AND N_BANK_ID=" + bankId + @"AND COMPANY_ID in (select cp.cmy_id
  from sys_company cp
 start with cp.cmy_id = " + User.USER_COMPANY + @"
connect by prior cp.cmy_id = cp.cmy_parentid)");
            if (whereStr != null)
            {
                sqlBuilder.AppendLine(" WHERE " + whereStr);
            }
            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        private Yx_Account ConvertToEntity(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new Yx_Account
            {
                Account_Id = Convert.ToDecimal(row["ACCOUNTI_ID"]),
                Account_No = Convert.ToString(row["ACCOUNTNO"])
            };
            return entity;
        }

        public List<Yx_Account> GetEntitysByCmyId(int cmyId)
        {
            var sql = @"SELECT
                            T1.*
                        FROM YX_ACCOUNT T1
                        WHERE
                            T1.COMPANY_ID = :COMPANY_ID
                        ORDER BY
                            T1.ACCOUNTI_ID";
            var parms = ParmsHelper.GetParms();
            parms.Add(":COMPANY_ID", cmyId, "int");
            var dt = Query(sql, parms);
            var entitys = new List<Yx_Account>();
            foreach (var item in dt.AsEnumerable())
            {
                var entity = new Yx_Account();
                entity.Account_Id = item.GetValue<int>("ACCOUNTI_ID");
                entity.Company_Id = item.GetValue<int>("COMPANY_ID");
                entity.Account_No = item.GetValue<string>("ACCOUNTNO");
                entity.Account_Type = item.GetValue<int>("ACCOUNTTYPE");
                entity.State = item.GetValue<int>("STATE");
                entity.Operator_Id = item.GetValue<int>("OPERATORID");
                entity.Operator_date = item.GetValue<DateTime>("OPERATORDATE");
                entity.Remark = item.GetValue<string>("REMARK") == null ? "" : item.GetValue<string>("REMARK");
                entity.Demartment_Id = item.GetValue<int>("DEPARTMENT_ID");
                entity.N_BANK_ID = item.GetValue<int?>("N_BANK_ID") == null ? -1 : item.GetValue<int>("N_BANK_ID");
                entity.N_AMOUNT = item.GetValue<decimal?>("N_AMOUNT") == null ? -1 : item.GetValue<int>("N_BANK_ID");
                entity.OPENBANK = item.GetValue<string>("OPENBANK") == null ? "" : item.GetValue<string>("OPENBANK");
                entitys.Add(entity);
            }
            return entitys;
        }

        #endregion 下拉列表框
    }
}